- In Data Flow Task ---> Drag and Drop Excel Source from Other Sources because we have our source in Excel File.
- Add the Connection Manager for the Source ---> (1) Double Click to Excel Source ---> (2) In Connection Manager ---> (3) Choose New for Excel Connection Manager.
- Browse for Excel File Path ---> Click to Browse
- Browse for the Excel File ---> Click Open
- Click to (1) “First row has column names” ---> (2) Click OK
- Choose the Sheet that contains Data
(1) Click to Columns ---> (2) Choose all Columns ---> (3) Click OK
- Use Conditional Split to split the Null value
- Drag and drop the Conditional Split from Commons to Data Flow Task ---> Connect the Excel Source to Conditional Split by dragging the blue line from Excel Source to Conditional Split.
- Double Click to Conditional Split --->(1) Give “Null Records” in Output Name ---> (2) Give “ISNULL(ListName) || ISNULL(CampaignID) || ISNULL(CampaignTitle) || ISNULL(IsActive) || ISNULL(CreatedDate) || ISNULL(SentDate) || ISNULL(Email) || ISNULL(EmailOpenCount) || ISNULL(Status)” in Condition ---> (3) Give “NotNull Records” in Default Output Name ---> (4) Click OK.
- (1) (2) Drag and Drop 2 Multicast to Data Control Flow
- (3) (4) connect the Conditional Split to the Multicasts by dragging the NotNull Records to the left Multicast and dragging the Null Records to the right Multicast ---> (5) Click Ok.
- Use Data Conversion to convert the data
- Drag and Drop the Data Conversion from Common to Data Flow Task ---> Connect the Multicast from the left side to Data Conversion by dragging the blue line from the left Multicast to the data Conversion.
- Double Click to Data Conversion
- (1) Choose all the columns ---> (2) Change the Data Type to String ---> (3) Change the Length to 255 ---> (4) Click OK.
- (1) Drag and Drop OLE DB Destination to Data Flow Task because we need to load the data to SQL Database Table
- (2) Connect Data Conversion to OLE DB Destination by dragging blue line from Data Conversion to OLE DB Destination.
- Double click to OLE DB Destination to create connection manager.
- (1) In Connection manager ---> (2) Click to New ---> (3) Click to New .
- (1) In Server Name -> Choose Server Name ---> (2) In Select or enter database name -> Choose the database that we want to load the data to ---> (3) Click OK.
- (1) In Data Connection click to the connection manager we have just created ---> (2) Click OK.
- In OLE DB Destination Editor -> In Name of the table or the view -> Click New.
- (1) In CREATE TABLE change the name to MailChimp ---> (2) Delete all the Copy of _ columns and the last comma ---> (3) Click OK.
- Click to mappings
- (1)
Reconnect the columns to Copy of _ (these are the columns from Data Conversion)
---> (2) Click OK.
- (1) Right click to the Package ---> (2) Click to Execute Package.
- The data is loaded from Excel Source to OLE DB Destination.
